<!DOCTYPE html>
<html>
<head>
  <meta charset="utf-8">
  

  
  <title>基础sql使用 | Threadv</title>
  <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
  <meta name="description" content="sql 示例：123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081">
<meta name="keywords" content="mysql">
<meta property="og:type" content="article">
<meta property="og:title" content="基础sql使用">
<meta property="og:url" content="http://yoursite.com/2019/04/08/mysql/sql_基础使用/index.html">
<meta property="og:site_name" content="Threadv">
<meta property="og:description" content="sql 示例：1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798991001">
<meta property="og:updated_time" content="2020-03-29T11:01:38.428Z">
<meta name="twitter:card" content="summary">
<meta name="twitter:title" content="基础sql使用">
<meta name="twitter:description" content="sql 示例：1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798991001">
  
    <link rel="alternate" href="/atom.xml" title="Threadv" type="application/atom+xml">
  
  
    <link rel="icon" href="/favicon.png">
  
  
    <link href="//fonts.googleapis.com/css?family=Source+Code+Pro" rel="stylesheet" type="text/css">
  
  <link rel="stylesheet" href="/css/style.css">
</head>

<body>
  <div id="container">
    <div id="wrap">
      <header id="header">
  <div id="banner"></div>
  <div id="header-outer" class="outer">
    <div id="header-title" class="inner">
      <h1 id="logo-wrap">
        <a href="/" id="logo">Threadv</a>
      </h1>
      
    </div>
    <div id="header-inner" class="inner">
      <nav id="main-nav">
        <a id="main-nav-toggle" class="nav-icon"></a>
        
          <a class="main-nav-link" href="/">Home</a>
        
          <a class="main-nav-link" href="/archives">Archives</a>
        
      </nav>
      <nav id="sub-nav">
        
          <a id="nav-rss-link" class="nav-icon" href="/atom.xml" title="RSS Feed"></a>
        
        <a id="nav-search-btn" class="nav-icon" title="Suche"></a>
      </nav>
      <div id="search-form-wrap">
        <form action="//google.com/search" method="get" accept-charset="UTF-8" class="search-form"><input type="search" name="q" class="search-form-input" placeholder="Search"><button type="submit" class="search-form-submit">&#xF002;</button><input type="hidden" name="sitesearch" value="http://yoursite.com"></form>
      </div>
    </div>
  </div>
</header>
      <div class="outer">
        <section id="main"><article id="post-mysql/sql_基础使用" class="article article-type-post" itemscope itemprop="blogPost">
  <div class="article-meta">
    <a href="/2019/04/08/mysql/sql_基础使用/" class="article-date">
  <time datetime="2019-04-08T07:42:06.000Z" itemprop="datePublished">2019-04-08</time>
</a>
    
  </div>
  <div class="article-inner">
    
    
      <header class="article-header">
        
  
    <h1 class="article-title" itemprop="name">
      基础sql使用
    </h1>
  

      </header>
    
    <div class="article-entry" itemprop="articleBody">
      
        <h5 id="sql-示例："><a href="#sql-示例：" class="headerlink" title="sql 示例："></a>sql 示例：</h5><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br><span class="line">61</span><br><span class="line">62</span><br><span class="line">63</span><br><span class="line">64</span><br><span class="line">65</span><br><span class="line">66</span><br><span class="line">67</span><br><span class="line">68</span><br><span class="line">69</span><br><span class="line">70</span><br><span class="line">71</span><br><span class="line">72</span><br><span class="line">73</span><br><span class="line">74</span><br><span class="line">75</span><br><span class="line">76</span><br><span class="line">77</span><br><span class="line">78</span><br><span class="line">79</span><br><span class="line">80</span><br><span class="line">81</span><br><span class="line">82</span><br><span class="line">83</span><br><span class="line">84</span><br><span class="line">85</span><br><span class="line">86</span><br><span class="line">87</span><br><span class="line">88</span><br><span class="line">89</span><br><span class="line">90</span><br><span class="line">91</span><br><span class="line">92</span><br><span class="line">93</span><br><span class="line">94</span><br><span class="line">95</span><br><span class="line">96</span><br><span class="line">97</span><br><span class="line">98</span><br><span class="line">99</span><br><span class="line">100</span><br><span class="line">101</span><br><span class="line">102</span><br><span class="line">103</span><br><span class="line">104</span><br><span class="line">105</span><br><span class="line">106</span><br><span class="line">107</span><br><span class="line">108</span><br><span class="line">109</span><br><span class="line">110</span><br><span class="line">111</span><br><span class="line">112</span><br><span class="line">113</span><br><span class="line">114</span><br><span class="line">115</span><br><span class="line">116</span><br><span class="line">117</span><br><span class="line">118</span><br><span class="line">119</span><br><span class="line">120</span><br><span class="line">121</span><br><span class="line">122</span><br><span class="line">123</span><br><span class="line">124</span><br><span class="line">125</span><br><span class="line">126</span><br><span class="line">127</span><br><span class="line">128</span><br><span class="line">129</span><br><span class="line">130</span><br><span class="line">131</span><br><span class="line">132</span><br><span class="line">133</span><br><span class="line">134</span><br><span class="line">135</span><br><span class="line">136</span><br><span class="line">137</span><br><span class="line">138</span><br><span class="line">139</span><br><span class="line">140</span><br><span class="line">141</span><br><span class="line">142</span><br><span class="line">143</span><br><span class="line">144</span><br><span class="line">145</span><br><span class="line">146</span><br><span class="line">147</span><br><span class="line">148</span><br><span class="line">149</span><br><span class="line">150</span><br><span class="line">151</span><br><span class="line">152</span><br><span class="line">153</span><br><span class="line">154</span><br><span class="line">155</span><br><span class="line">156</span><br><span class="line">157</span><br><span class="line">158</span><br><span class="line">159</span><br><span class="line">160</span><br><span class="line">161</span><br><span class="line">162</span><br><span class="line">163</span><br><span class="line">164</span><br><span class="line">165</span><br><span class="line">166</span><br><span class="line">167</span><br><span class="line">168</span><br><span class="line">169</span><br><span class="line">170</span><br><span class="line">171</span><br><span class="line">172</span><br><span class="line">173</span><br><span class="line">174</span><br><span class="line">175</span><br><span class="line">176</span><br><span class="line">177</span><br><span class="line">178</span><br><span class="line">179</span><br><span class="line">180</span><br><span class="line">181</span><br><span class="line">182</span><br><span class="line">183</span><br><span class="line">184</span><br><span class="line">185</span><br><span class="line">186</span><br><span class="line">187</span><br><span class="line">188</span><br><span class="line">189</span><br><span class="line">190</span><br><span class="line">191</span><br><span class="line">192</span><br><span class="line">193</span><br><span class="line">194</span><br><span class="line">195</span><br><span class="line">196</span><br><span class="line">197</span><br><span class="line">198</span><br><span class="line">199</span><br><span class="line">200</span><br><span class="line">201</span><br><span class="line">202</span><br><span class="line">203</span><br><span class="line">204</span><br><span class="line">205</span><br><span class="line">206</span><br><span class="line">207</span><br><span class="line">208</span><br><span class="line">209</span><br><span class="line">210</span><br><span class="line">211</span><br><span class="line">212</span><br><span class="line">213</span><br><span class="line">214</span><br><span class="line">215</span><br><span class="line">216</span><br><span class="line">217</span><br><span class="line">218</span><br><span class="line">219</span><br><span class="line">220</span><br><span class="line">221</span><br><span class="line">222</span><br><span class="line">223</span><br><span class="line">224</span><br><span class="line">225</span><br><span class="line">226</span><br><span class="line">227</span><br><span class="line">228</span><br><span class="line">229</span><br><span class="line">230</span><br><span class="line">231</span><br><span class="line">232</span><br><span class="line">233</span><br><span class="line">234</span><br><span class="line">235</span><br><span class="line">236</span><br><span class="line">237</span><br><span class="line">238</span><br><span class="line">239</span><br><span class="line">240</span><br><span class="line">241</span><br><span class="line">242</span><br><span class="line">243</span><br><span class="line">244</span><br><span class="line">245</span><br><span class="line">246</span><br><span class="line">247</span><br><span class="line">248</span><br><span class="line">249</span><br><span class="line">250</span><br><span class="line">251</span><br><span class="line">252</span><br><span class="line">253</span><br><span class="line">254</span><br><span class="line">255</span><br><span class="line">256</span><br><span class="line">257</span><br><span class="line">258</span><br><span class="line">259</span><br><span class="line">260</span><br><span class="line">261</span><br><span class="line">262</span><br><span class="line">263</span><br><span class="line">264</span><br><span class="line">265</span><br><span class="line">266</span><br><span class="line">267</span><br><span class="line">268</span><br><span class="line">269</span><br><span class="line">270</span><br><span class="line">271</span><br><span class="line">272</span><br><span class="line">273</span><br><span class="line">274</span><br><span class="line">275</span><br><span class="line">276</span><br><span class="line">277</span><br><span class="line">278</span><br><span class="line">279</span><br><span class="line">280</span><br><span class="line">281</span><br><span class="line">282</span><br><span class="line">283</span><br><span class="line">284</span><br><span class="line">285</span><br><span class="line">286</span><br><span class="line">287</span><br><span class="line">288</span><br><span class="line">289</span><br><span class="line">290</span><br><span class="line">291</span><br><span class="line">292</span><br><span class="line">293</span><br><span class="line">294</span><br><span class="line">295</span><br><span class="line">296</span><br><span class="line">297</span><br><span class="line">298</span><br><span class="line">299</span><br><span class="line">300</span><br><span class="line">301</span><br><span class="line">302</span><br><span class="line">303</span><br><span class="line">304</span><br><span class="line">305</span><br><span class="line">306</span><br><span class="line">307</span><br><span class="line">308</span><br><span class="line">309</span><br><span class="line">310</span><br><span class="line">311</span><br><span class="line">312</span><br><span class="line">313</span><br><span class="line">314</span><br><span class="line">315</span><br><span class="line">316</span><br><span class="line">317</span><br><span class="line">318</span><br><span class="line">319</span><br><span class="line">320</span><br><span class="line">321</span><br><span class="line">322</span><br><span class="line">323</span><br><span class="line">324</span><br><span class="line">325</span><br><span class="line">326</span><br><span class="line">327</span><br><span class="line">328</span><br><span class="line">329</span><br><span class="line">330</span><br></pre></td><td class="code"><pre><span class="line">Student(Sid,Sname,Sage,Ssex)学生表</span><br><span class="line">Sid：学号</span><br><span class="line">Sname：学生姓名</span><br><span class="line">Sage：学生年龄</span><br><span class="line">Ssex：学生性别</span><br><span class="line">Course(Cid,Cname,Tid)课程表</span><br><span class="line">Cid：课程编号</span><br><span class="line">Cname：课程名称</span><br><span class="line">Tid：教师编号</span><br><span class="line">SC(Sid,Cid,score)成绩表</span><br><span class="line">Sid：学号</span><br><span class="line">Cid：课程编号</span><br><span class="line">score：成绩</span><br><span class="line">Teacher(Tid,Tname)教师表</span><br><span class="line">Tid：教师编号：</span><br><span class="line">Tname：教师名字</span><br><span class="line"></span><br><span class="line"></span><br><span class="line">问题：</span><br><span class="line">1、查询“001”课程比“002”课程成绩高的所有学生的学号</span><br><span class="line"><span class="keyword">select</span> a.Sid <span class="keyword">from</span> (<span class="keyword">select</span> <span class="keyword">Sid</span>,score <span class="keyword">from</span> SC <span class="keyword">where</span> Cid=<span class="string">'001'</span>)a, (<span class="keyword">select</span> <span class="keyword">sid</span>,score <span class="keyword">from</span> SC <span class="keyword">where</span> cid=<span class="string">'002'</span>)b <span class="keyword">Where</span> a.score&gt;b.score <span class="keyword">and</span> a.sid = b.sid;</span><br><span class="line"></span><br><span class="line">2、查询平均成绩大于60分的同学的学号和平均成绩</span><br><span class="line"><span class="keyword">select</span> <span class="keyword">Sid</span>, <span class="keyword">avg</span>(score) <span class="keyword">from</span> sc <span class="keyword">group</span> <span class="keyword">by</span> <span class="keyword">Sid</span> <span class="keyword">having</span> <span class="keyword">avg</span>(score)&gt;<span class="number">60</span></span><br><span class="line"></span><br><span class="line"><span class="number">3</span>、查询所有同学的学号、姓名、选课数、总成绩</span><br><span class="line"><span class="keyword">select</span> student.Sid, student.Sname, <span class="keyword">count</span>(sc.Cid), <span class="keyword">sum</span>(score) <span class="keyword">from</span> student <span class="keyword">left</span> <span class="keyword">outer</span> <span class="keyword">join</span> SC <span class="keyword">on</span> student.Sid = SC.Sid <span class="keyword">group</span> <span class="keyword">by</span> Student.Sid, Sname</span><br><span class="line"></span><br><span class="line"><span class="number">4</span>、查询姓‘李’的老师的个数：</span><br><span class="line"><span class="keyword">select</span> <span class="keyword">count</span>(<span class="keyword">distinct</span>(Tname)) </span><br><span class="line"><span class="keyword">from</span> teacher </span><br><span class="line"><span class="keyword">where</span> tname <span class="keyword">like</span> <span class="string">'李%'</span>;</span><br><span class="line"></span><br><span class="line">5、查询没有学过“叶平”老师可的同学的学号、姓名：</span><br><span class="line"><span class="keyword">select</span> student.Sid, student.Sname </span><br><span class="line"><span class="keyword">from</span> Student </span><br><span class="line"><span class="keyword">where</span> <span class="keyword">Sid</span> <span class="keyword">not</span> <span class="keyword">in</span> (<span class="keyword">select</span> <span class="keyword">distinct</span>(SC.Sid) <span class="keyword">from</span> SC,Course,Teacher </span><br><span class="line"><span class="keyword">where</span> sc.cid=course.cid <span class="keyword">AND</span> teacher.Tid=course.Tid <span class="keyword">AND</span> Teahcer.Tname =<span class="string">'叶平'</span>);</span><br><span class="line"></span><br><span class="line">6、查询学过“叶平”老师所教的所有课的同学的学号、姓名：</span><br><span class="line"><span class="keyword">select</span> <span class="keyword">Sid</span>,Sname   <span class="keyword">from</span> Student    </span><br><span class="line"><span class="keyword">where</span> <span class="keyword">Sid</span> <span class="keyword">in</span> (<span class="keyword">select</span> <span class="keyword">Sid</span> <span class="keyword">from</span> SC ,Course ,Teacher </span><br><span class="line"><span class="keyword">where</span> SC.Cid=Course.Cid <span class="keyword">and</span> Teacher.Tid=Course.Tid </span><br><span class="line"><span class="keyword">and</span> Teacher.Tname=<span class="string">'叶平'</span> <span class="keyword">group</span> <span class="keyword">by</span> <span class="keyword">Sid</span> </span><br><span class="line"><span class="keyword">having</span> <span class="keyword">count</span>(SC.Cid)=(<span class="keyword">select</span> <span class="keyword">count</span>(Cid) <span class="keyword">from</span> Course,Teacher  </span><br><span class="line"><span class="keyword">where</span> Teacher.Tid=Course.Tid <span class="keyword">and</span> Tname=<span class="string">'叶平'</span>));</span><br><span class="line"></span><br><span class="line">7、查询学过“011”并且也学过编号“002”课程的同学的学号、姓名：</span><br><span class="line"><span class="keyword">select</span> Student.Sid,Student.Sname </span><br><span class="line"><span class="keyword">from</span> Student,SC <span class="keyword">where</span> Student.Sid=SC.Sid </span><br><span class="line"><span class="keyword">and</span> SC.Cid=<span class="string">'001'</span><span class="keyword">and</span> </span><br><span class="line"><span class="keyword">exists</span>( <span class="keyword">Select</span> * <span class="keyword">from</span> SC <span class="keyword">as</span> SC_2 <span class="keyword">where</span> SC_2.Sid=SC.Sid <span class="keyword">and</span> SC_2.Cid=<span class="string">'002'</span>);</span><br><span class="line"></span><br><span class="line">8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名：</span><br><span class="line"><span class="keyword">Select</span> <span class="keyword">Sid</span>,Sname </span><br><span class="line"><span class="keyword">from</span> (<span class="keyword">select</span> Student.Sid,Student.Sname,score ,</span><br><span class="line">(<span class="keyword">select</span> score <span class="keyword">from</span> SC SC_2 <span class="keyword">where</span> SC_2.Sid=Student.Sid <span class="keyword">and</span> SC_2.Cid=<span class="string">'002'</span>) score2    </span><br><span class="line"><span class="keyword">from</span> Student,SC </span><br><span class="line"><span class="keyword">where</span> Student.Sid=SC.Sid <span class="keyword">and</span> Cid=<span class="string">'001'</span>) S_2 </span><br><span class="line"><span class="keyword">where</span> score2 &lt; score; </span><br><span class="line"></span><br><span class="line">9、查询所有课程成绩小于60的同学的学号、姓名：</span><br><span class="line"><span class="keyword">select</span> <span class="keyword">Sid</span>, sname </span><br><span class="line"><span class="keyword">from</span> student </span><br><span class="line"><span class="keyword">where</span> <span class="keyword">sid</span> <span class="keyword">not</span> <span class="keyword">in</span> </span><br><span class="line">(<span class="keyword">select</span> student.sid <span class="keyword">from</span> student, sc <span class="keyword">where</span> s.sid = sc.sid <span class="keyword">and</span> score&gt;<span class="number">60</span>);</span><br><span class="line"></span><br><span class="line">10、查询没有学全所有课的同学的学号、姓名：</span><br><span class="line"><span class="keyword">select</span> student.sid, student.sname </span><br><span class="line"><span class="keyword">from</span> student, sc</span><br><span class="line"><span class="keyword">where</span> student.sid=sc.sid </span><br><span class="line"><span class="keyword">group</span> <span class="keyword">by</span> student.sid, student.sname </span><br><span class="line"><span class="keyword">having</span> <span class="keyword">count</span>(cid)&lt;(<span class="keyword">select</span> <span class="keyword">count</span>(cid) <span class="keyword">from</span> course);</span><br><span class="line"></span><br><span class="line">11、查询至少有一门课与学号为“1001”同学所学相同的同学的学号和姓名：</span><br><span class="line"><span class="keyword">select</span> <span class="keyword">sid</span>, Sname </span><br><span class="line"><span class="keyword">from</span> Student, SC </span><br><span class="line"><span class="keyword">where</span> student.sid = sc.sid </span><br><span class="line"><span class="keyword">and</span> cid <span class="keyword">in</span> (<span class="keyword">select</span> cid <span class="keyword">from</span> SC <span class="keyword">where</span> <span class="keyword">sid</span>=<span class="string">'1001'</span>);</span><br><span class="line"></span><br><span class="line">12、查询至少学过学号为“001”同学所有一门课的其他同学学号和姓名；</span><br><span class="line"><span class="keyword">select</span> <span class="keyword">distinct</span> sc.sid , sname </span><br><span class="line"><span class="keyword">from</span> student, sc </span><br><span class="line"><span class="keyword">where</span> student.sid=sc.sid </span><br><span class="line"><span class="keyword">and</span> cid <span class="keyword">in</span> (<span class="keyword">select</span> Cid <span class="keyword">from</span> sc <span class="keyword">where</span> <span class="keyword">sid</span>=<span class="string">'001'</span>);</span><br><span class="line"></span><br><span class="line">13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩：</span><br><span class="line"><span class="keyword">Update</span> Sc <span class="keyword">Set</span> Score=(<span class="keyword">Select</span> <span class="keyword">Avg</span>(s2_Score) <span class="keyword">From</span> sc s2 <span class="keyword">Where</span> s2.cid=sc.cid)  </span><br><span class="line"><span class="keyword">Where</span> cid <span class="keyword">IN</span></span><br><span class="line">(<span class="keyword">Select</span> cid <span class="keyword">From</span> sc cs <span class="keyword">INNER</span> <span class="keyword">JOIN</span> Teacher tc <span class="keyword">ON</span> cs.tid=tc.tid <span class="keyword">WHERE</span> tname =<span class="string">'叶平'</span>)</span><br><span class="line"></span><br><span class="line"><span class="number">14</span>、查询和“<span class="number">1002</span>”号的同学学习的课程完全相同的其他同学学号和姓名：</span><br><span class="line"><span class="keyword">select</span> <span class="keyword">sid</span> <span class="keyword">from</span> sc <span class="keyword">where</span> cid  <span class="keyword">in</span> </span><br><span class="line">(<span class="keyword">select</span> cid <span class="keyword">from</span> sc <span class="keyword">where</span> <span class="keyword">sid</span>=<span class="string">'1002'</span>) </span><br><span class="line"><span class="keyword">group</span> <span class="keyword">by</span> <span class="keyword">sid</span> <span class="keyword">having</span> <span class="keyword">count</span>(*)=</span><br><span class="line">(<span class="keyword">select</span> <span class="keyword">count</span>(*) <span class="keyword">from</span> sc <span class="keyword">where</span> <span class="keyword">sid</span>=<span class="string">'1002'</span>);</span><br><span class="line"></span><br><span class="line">15、删除学习“叶平”老师课的SC表记录：</span><br><span class="line">delect sc </span><br><span class="line">from course, Teacher </span><br><span class="line">where course.cid=sc.cid </span><br><span class="line">and course.tid=teacher.tid </span><br><span class="line">and tname='叶平';</span><br><span class="line"></span><br><span class="line">16、向SC表中插入一些记录，这些记录要求符合以下条件：没有上过编号“003”课程的同学学号、002号课的平均成绩：</span><br><span class="line"><span class="keyword">Insert</span> SC <span class="keyword">select</span> <span class="keyword">Sid</span>,<span class="string">'002'</span>,</span><br><span class="line">(<span class="keyword">Select</span> <span class="keyword">avg</span>(score) <span class="keyword">from</span> SC <span class="keyword">where</span> Cid=<span class="string">'002'</span>) </span><br><span class="line"><span class="keyword">from</span> Student <span class="keyword">where</span> <span class="keyword">Sid</span> <span class="keyword">not</span> <span class="keyword">in</span> (<span class="keyword">Select</span> <span class="keyword">Sid</span> <span class="keyword">from</span> SC <span class="keyword">where</span> Cid=<span class="string">'002'</span>);</span><br><span class="line"></span><br><span class="line">17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩，按如下形式显示：学生ID，数据库，企业管理，英语，有效课程数，有效平均分：</span><br><span class="line"><span class="keyword">select</span> <span class="keyword">sid</span> <span class="keyword">as</span> 学生<span class="keyword">ID</span>,</span><br><span class="line">(<span class="keyword">select</span> score <span class="keyword">from</span> sc <span class="keyword">where</span> sc.sid=t.sid <span class="keyword">and</span> cid=<span class="string">'004'</span>) <span class="keyword">as</span> 数据库,</span><br><span class="line">(<span class="keyword">select</span> score <span class="keyword">from</span> sc <span class="keyword">where</span> sc.sid=t.sid <span class="keyword">and</span> cid=<span class="string">'001'</span>) <span class="keyword">as</span> 企业管理,</span><br><span class="line">(<span class="keyword">select</span> score <span class="keyword">from</span> sc <span class="keyword">where</span> sc.sid=t.sid <span class="keyword">and</span> cid=<span class="string">'006'</span>) <span class="keyword">as</span> 英语,</span><br><span class="line"><span class="keyword">count</span>(*) <span class="keyword">as</span> 有效课程数, <span class="keyword">avg</span>(t.score) <span class="keyword">as</span> 平局成绩</span><br><span class="line"><span class="keyword">from</span> sc <span class="keyword">as</span> t</span><br><span class="line"><span class="keyword">group</span> <span class="keyword">by</span> <span class="keyword">sid</span> </span><br><span class="line"><span class="keyword">order</span> <span class="keyword">by</span> <span class="keyword">avg</span>(t.score)</span><br><span class="line"></span><br><span class="line"><span class="number">18</span>、查询各科成绩最高和最低的分： 以如下的形式显示：课程<span class="keyword">ID</span>，最高分，最低分</span><br><span class="line"><span class="keyword">select</span> L.cid <span class="keyword">as</span> 课程<span class="keyword">ID</span>, L.score <span class="keyword">as</span> 最高分,</span><br><span class="line">R.score <span class="keyword">as</span> 最低分</span><br><span class="line"><span class="keyword">from</span> sc L, sc R </span><br><span class="line"><span class="keyword">where</span> L.cid = R.cid </span><br><span class="line"><span class="keyword">and</span> L.score = (<span class="keyword">select</span> <span class="keyword">max</span>(IL.score) </span><br><span class="line">        <span class="keyword">from</span> sc IL, student <span class="keyword">as</span> IM </span><br><span class="line">        <span class="keyword">where</span> L.cid=IL.cid <span class="keyword">and</span> IM.sid=IL.sid</span><br><span class="line">        <span class="keyword">group</span> <span class="keyword">by</span> IL.cid)</span><br><span class="line"><span class="keyword">and</span> R.score = (<span class="keyword">select</span> <span class="keyword">min</span>(IR.score)</span><br><span class="line">        <span class="keyword">from</span> sc <span class="keyword">as</span> IR</span><br><span class="line">        <span class="keyword">where</span> R.cid=IR.cid</span><br><span class="line">        <span class="keyword">group</span> <span class="keyword">by</span> IR.cid);</span><br><span class="line"></span><br><span class="line">19、按各科平均成绩从低到高和及格率的百分数从高到低顺序：</span><br><span class="line"><span class="keyword">SELECT</span> t.Cid <span class="keyword">AS</span> 课程号,</span><br><span class="line"><span class="keyword">max</span>(course.Cname)<span class="keyword">AS</span> 课程名,</span><br><span class="line"><span class="keyword">isnull</span>(<span class="keyword">AVG</span>(score),<span class="number">0</span>) <span class="keyword">AS</span> 平均成绩,</span><br><span class="line"><span class="number">100</span> * <span class="keyword">SUM</span>(<span class="keyword">CASE</span> <span class="keyword">WHEN</span>  <span class="keyword">isnull</span>(score,<span class="number">0</span>)&gt;=<span class="number">60</span> <span class="keyword">THEN</span> <span class="number">1</span> <span class="keyword">ELSE</span> <span class="number">0</span> <span class="keyword">END</span>)/<span class="keyword">COUNT</span>(*) <span class="keyword">AS</span> 及格百分数     </span><br><span class="line"><span class="keyword">FROM</span> SC T,Course     </span><br><span class="line"><span class="keyword">where</span> t.Cid=course.Cid     </span><br><span class="line"><span class="keyword">GROUP</span> <span class="keyword">BY</span> t.Cid      </span><br><span class="line"><span class="keyword">ORDER</span> <span class="keyword">BY</span> <span class="number">100</span> * <span class="keyword">SUM</span>(<span class="keyword">CASE</span> <span class="keyword">WHEN</span>  <span class="keyword">isnull</span>(score,<span class="number">0</span>)&gt;=<span class="number">60</span> <span class="keyword">THEN</span> <span class="number">1</span> <span class="keyword">ELSE</span> <span class="number">0</span> <span class="keyword">END</span>)/<span class="keyword">COUNT</span>(*) <span class="keyword">DESC</span> </span><br><span class="line"></span><br><span class="line"><span class="number">20</span>、查询如下课程平均成绩和及格率的百分数(用”<span class="number">1</span>行”显示): 企业管理（<span class="number">001</span>），马克思（<span class="number">002</span>），OO&amp;UML （<span class="number">003</span>），数据库（<span class="number">004</span>）：</span><br><span class="line"><span class="number">21</span>、查询不同老师所教不同课程平均分从高到低显示：</span><br><span class="line"> <span class="keyword">SELECT</span> <span class="keyword">max</span>(Z.Tid) <span class="keyword">AS</span> 教师<span class="keyword">ID</span>,</span><br><span class="line"> <span class="keyword">MAX</span>(Z.Tname) <span class="keyword">AS</span> 教师姓名,</span><br><span class="line"> C.Cid <span class="keyword">AS</span> 课程<span class="keyword">ID</span>,</span><br><span class="line"> <span class="keyword">AVG</span>(Score) <span class="keyword">AS</span> 平均成绩     </span><br><span class="line"> <span class="keyword">FROM</span> SC <span class="keyword">AS</span> T,Course <span class="keyword">AS</span> C ,Teacher <span class="keyword">AS</span> Z    </span><br><span class="line"> <span class="keyword">where</span> T.Cid=C.Cid <span class="keyword">and</span> C.Tid=Z.Tid   </span><br><span class="line"> <span class="keyword">GROUP</span> <span class="keyword">BY</span> C.Cid    </span><br><span class="line"> <span class="keyword">ORDER</span> <span class="keyword">BY</span> <span class="keyword">AVG</span>(Score) <span class="keyword">DESC</span></span><br><span class="line"></span><br><span class="line"><span class="number">22</span>、查询如下课程成绩第<span class="number">3</span>名到第<span class="number">6</span>名的学生成绩单：企业管理(<span class="number">001</span>)，马克思(<span class="number">002</span>)，UML(<span class="number">003</span>)，数据库(<span class="number">004</span>)：</span><br><span class="line"><span class="number">23</span>、统计下列各科成绩，各分数段人数：课程<span class="keyword">ID</span>，课程名称，[<span class="number">100</span><span class="number">-85</span>],[<span class="number">85</span><span class="number">-70</span>],[<span class="number">70</span><span class="number">-60</span>],[ 小于<span class="number">60</span>] ：</span><br><span class="line"><span class="keyword">SELECT</span> SC.Cid <span class="keyword">as</span> 课程<span class="keyword">ID</span>, Cname <span class="keyword">as</span> 课程名称,</span><br><span class="line"><span class="keyword">SUM</span>(<span class="keyword">CASE</span> <span class="keyword">WHEN</span> score <span class="keyword">BETWEEN</span> <span class="number">85</span> <span class="keyword">AND</span> <span class="number">100</span> <span class="keyword">THEN</span> <span class="number">1</span> <span class="keyword">ELSE</span> <span class="number">0</span> <span class="keyword">END</span>) <span class="keyword">AS</span> [<span class="number">100</span> - <span class="number">85</span>]  ,</span><br><span class="line"><span class="keyword">SUM</span>(<span class="keyword">CASE</span> <span class="keyword">WHEN</span> score <span class="keyword">BETWEEN</span> <span class="number">70</span> <span class="keyword">AND</span> <span class="number">85</span> <span class="keyword">THEN</span> <span class="number">1</span> <span class="keyword">ELSE</span> <span class="number">0</span> <span class="keyword">END</span>) <span class="keyword">AS</span> [<span class="number">85</span> - <span class="number">70</span>],</span><br><span class="line"><span class="keyword">SUM</span>(<span class="keyword">CASE</span> <span class="keyword">WHEN</span> score <span class="keyword">BETWEEN</span> <span class="number">60</span> <span class="keyword">AND</span> <span class="number">70</span> <span class="keyword">THEN</span> <span class="number">1</span> <span class="keyword">ELSE</span> <span class="number">0</span> <span class="keyword">END</span>) <span class="keyword">AS</span> [<span class="number">70</span> - <span class="number">60</span>],</span><br><span class="line"><span class="keyword">SUM</span>(<span class="keyword">CASE</span> <span class="keyword">WHEN</span> score &lt; <span class="number">60</span> <span class="keyword">THEN</span> <span class="number">1</span> <span class="keyword">ELSE</span> <span class="number">0</span> <span class="keyword">END</span>) <span class="keyword">AS</span> [<span class="number">60</span> -]     </span><br><span class="line"><span class="keyword">FROM</span> SC,Course     </span><br><span class="line"><span class="keyword">where</span> SC.Cid=Course.Cid     </span><br><span class="line"><span class="keyword">GROUP</span> <span class="keyword">BY</span> SC.Cid,Cname;</span><br><span class="line"></span><br><span class="line">24、查询学生平均成绩及其名次：</span><br><span class="line"><span class="keyword">SELECT</span> <span class="number">1</span>+(<span class="keyword">SELECT</span> <span class="keyword">COUNT</span>( <span class="keyword">distinct</span> 平均成绩)                </span><br><span class="line"><span class="keyword">FROM</span> (<span class="keyword">SELECT</span> <span class="keyword">Sid</span>,<span class="keyword">AVG</span>(score) <span class="keyword">AS</span> 平均成绩                       </span><br><span class="line"><span class="keyword">FROM</span> SC                   </span><br><span class="line"><span class="keyword">GROUP</span> <span class="keyword">BY</span> <span class="keyword">Sid</span>  ) <span class="keyword">AS</span> T1  <span class="keyword">WHERE</span> 平均成绩 &gt; T2.平均成绩) <span class="keyword">as</span> 名次,       </span><br><span class="line"><span class="keyword">Sid</span> <span class="keyword">as</span> 学生学号,平均成绩      </span><br><span class="line"><span class="keyword">FROM</span> (<span class="keyword">SELECT</span> <span class="keyword">Sid</span>,<span class="keyword">AVG</span>(score) 平均成绩             </span><br><span class="line"><span class="keyword">FROM</span> SC         </span><br><span class="line"><span class="keyword">GROUP</span> <span class="keyword">BY</span> <span class="keyword">Sid</span> ) <span class="keyword">AS</span> T2      </span><br><span class="line"><span class="keyword">ORDER</span> <span class="keyword">BY</span> 平均成绩 <span class="keyword">desc</span>; </span><br><span class="line"></span><br><span class="line">25、查询各科成绩前三名的记录（不考虑成绩并列情况）：</span><br><span class="line"><span class="keyword">SELECT</span> t1.Sid <span class="keyword">as</span> 学生<span class="keyword">ID</span>,t1.Cid <span class="keyword">as</span> 课程<span class="keyword">ID</span>,Score <span class="keyword">as</span> 分数       </span><br><span class="line"><span class="keyword">FROM</span> SC t1        </span><br><span class="line"><span class="keyword">WHERE</span> score <span class="keyword">IN</span> </span><br><span class="line">(<span class="keyword">SELECT</span> TOP <span class="number">3</span> score               </span><br><span class="line"><span class="keyword">FROM</span> SC               </span><br><span class="line"><span class="keyword">WHERE</span> t1.Cid= Cid             </span><br><span class="line"><span class="keyword">ORDER</span> <span class="keyword">BY</span> score <span class="keyword">DESC</span>)        </span><br><span class="line"></span><br><span class="line"><span class="number">26</span>、查询每门课程被选修的学生数：</span><br><span class="line"><span class="keyword">select</span> cid, <span class="keyword">count</span>(<span class="keyword">sid</span>) </span><br><span class="line"><span class="keyword">from</span> sc </span><br><span class="line"><span class="keyword">group</span> <span class="keyword">by</span> cid;</span><br><span class="line"></span><br><span class="line">27、查询出只选修一门课程的全部学生的学号和姓名：</span><br><span class="line"><span class="keyword">select</span> sc.sid, student.sname, <span class="keyword">count</span>(cid) <span class="keyword">as</span> 选课数</span><br><span class="line"><span class="keyword">from</span> sc,student </span><br><span class="line"><span class="keyword">where</span> sc.sid =student.sid </span><br><span class="line"><span class="keyword">group</span> <span class="keyword">by</span> sc.sid,Student.sname </span><br><span class="line"><span class="keyword">having</span> <span class="keyword">count</span>(cid)=<span class="number">1</span>;</span><br><span class="line"></span><br><span class="line">28、查询男生、女生人数：</span><br><span class="line"><span class="keyword">select</span> <span class="keyword">count</span>(Ssex) <span class="keyword">as</span> 男生人数 </span><br><span class="line"><span class="keyword">from</span> student </span><br><span class="line"><span class="keyword">group</span> <span class="keyword">by</span> Ssex </span><br><span class="line"><span class="keyword">having</span> Ssex=<span class="string">'男'</span>；</span><br><span class="line"><span class="keyword">select</span> <span class="keyword">count</span>(Ssex) <span class="keyword">as</span> 女生人数 </span><br><span class="line"><span class="keyword">from</span> student </span><br><span class="line"><span class="keyword">group</span> <span class="keyword">by</span> Ssex </span><br><span class="line"><span class="keyword">having</span> Ssex=<span class="string">'女'</span>;</span><br><span class="line"></span><br><span class="line">29、查询姓“张”的学生名单：</span><br><span class="line"><span class="keyword">select</span> sname </span><br><span class="line"><span class="keyword">from</span> student </span><br><span class="line"><span class="keyword">where</span> sname <span class="keyword">like</span> <span class="string">'张%'</span>;</span><br><span class="line"></span><br><span class="line">30、查询同名同姓的学生名单，并统计同名人数：</span><br><span class="line"><span class="keyword">select</span> sanme,<span class="keyword">count</span>(*) </span><br><span class="line"><span class="keyword">from</span> student </span><br><span class="line"><span class="keyword">group</span> <span class="keyword">by</span> sname </span><br><span class="line">havang <span class="keyword">count</span>(*)&gt;<span class="number">1</span>;</span><br><span class="line"></span><br><span class="line">31、1981年出生的学生名单（注：student表中sage列的类型是datetime）:</span><br><span class="line"><span class="keyword">select</span> sname, <span class="keyword">convert</span>(<span class="built_in">char</span>(<span class="number">11</span>),<span class="keyword">DATEPART</span>(<span class="keyword">year</span>,sage)) <span class="keyword">as</span> age</span><br><span class="line"><span class="keyword">from</span> student </span><br><span class="line"><span class="keyword">where</span> <span class="keyword">convert</span>(<span class="built_in">char</span>(<span class="number">11</span>),<span class="keyword">DATEPART</span>(<span class="keyword">year</span>,Sage))=<span class="string">'1981'</span>;</span><br><span class="line"></span><br><span class="line">32、查询平均成绩大于85的所有学生的学号、姓名和平均成绩：</span><br><span class="line"><span class="keyword">select</span> Sname,SC.Sid ,<span class="keyword">avg</span>(score)     </span><br><span class="line"><span class="keyword">from</span> Student,SC      </span><br><span class="line"><span class="keyword">where</span> Student.Sid=SC.Sid </span><br><span class="line"><span class="keyword">group</span> <span class="keyword">by</span> SC.Sid,Sname </span><br><span class="line"><span class="keyword">having</span>    <span class="keyword">avg</span>(score)&gt;<span class="number">85</span>;</span><br><span class="line"></span><br><span class="line">33、查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列：</span><br><span class="line"><span class="keyword">select</span> Cid, <span class="keyword">avg</span>(score) </span><br><span class="line"><span class="keyword">from</span> sc </span><br><span class="line"><span class="keyword">group</span> <span class="keyword">by</span> cid </span><br><span class="line"><span class="keyword">order</span> <span class="keyword">by</span> <span class="keyword">avg</span>(score), cid <span class="keyword">desc</span>;</span><br><span class="line"></span><br><span class="line">34、查询课程名称为“数据库”，且分数低于60的学生名字和分数：</span><br><span class="line"><span class="keyword">select</span> sname, <span class="keyword">isnull</span>(score,<span class="number">0</span>) </span><br><span class="line"><span class="keyword">from</span> student, sc ,course </span><br><span class="line"><span class="keyword">where</span> sc.sid=student.sid  <span class="keyword">and</span> sc.cid=course.cid <span class="keyword">and</span> course.cname=<span class="string">'数据库'</span> <span class="keyword">and</span> score&lt;<span class="number">60</span>;</span><br><span class="line"></span><br><span class="line">35、查询所有学生的选课情况：</span><br><span class="line"><span class="keyword">select</span> sc.sid,sc.cid,sname,cname </span><br><span class="line"><span class="keyword">from</span> sc,student course </span><br><span class="line"><span class="keyword">where</span> sc.sid=student.sid <span class="keyword">and</span> sc.cid=course.cid;</span><br><span class="line"></span><br><span class="line">36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数：</span><br><span class="line"><span class="keyword">select</span> <span class="keyword">distinct</span> student.sid,student.sname,sc.cid,sc.score </span><br><span class="line"><span class="keyword">from</span> student,sc </span><br><span class="line"><span class="keyword">where</span> sc.score&gt;=<span class="number">70</span> <span class="keyword">and</span> sc.sid=student.sid;</span><br><span class="line"></span><br><span class="line">37、查询不及格的课程，并按课程号从大到小的排列：</span><br><span class="line"><span class="keyword">select</span> cid </span><br><span class="line"><span class="keyword">from</span> sc </span><br><span class="line"><span class="keyword">where</span> score&lt;<span class="number">60</span> </span><br><span class="line"><span class="keyword">order</span> <span class="keyword">by</span> cid;</span><br><span class="line"></span><br><span class="line">38、查询课程编号为“003”且课程成绩在80分以上的学生的学号和姓名：</span><br><span class="line"><span class="keyword">select</span> sc.sid,student.sname </span><br><span class="line"><span class="keyword">from</span> sc,student </span><br><span class="line"><span class="keyword">where</span> sc.sid=student.sid <span class="keyword">and</span> score&gt;<span class="number">80</span> <span class="keyword">and</span> cid=<span class="string">'003'</span>;</span><br><span class="line"></span><br><span class="line">39、求选了课程的学生人数：</span><br><span class="line"><span class="keyword">select</span> <span class="keyword">count</span>(*) <span class="keyword">from</span> sc;</span><br><span class="line"></span><br><span class="line">40、查询选修“叶平”老师所授课程的学生中，成绩最高的学生姓名及其成绩：</span><br><span class="line"><span class="keyword">select</span> student.sname,score </span><br><span class="line"><span class="keyword">from</span> student,sc,course c, teacher </span><br><span class="line"><span class="keyword">where</span> student.sid=sc.Sid <span class="keyword">and</span> sc.cid=c.cid</span><br><span class="line"><span class="keyword">and</span> c.Tid=teacher.Tid</span><br><span class="line"><span class="keyword">and</span> teacher.tname=<span class="string">'叶平'</span> </span><br><span class="line"><span class="keyword">and</span> sc.score=(<span class="keyword">select</span> <span class="keyword">max</span>(score) <span class="keyword">from</span> sc <span class="keyword">where</span> cid=c.cid);</span><br><span class="line"></span><br><span class="line">41、查询各个课程及相应的选修人数：</span><br><span class="line"><span class="keyword">select</span> <span class="keyword">count</span>(*) <span class="keyword">from</span> sc <span class="keyword">group</span> <span class="keyword">by</span> cid;</span><br><span class="line"></span><br><span class="line">42、查询不同课程成绩相同的学生和学号、课程号、学生成绩：</span><br><span class="line"><span class="keyword">select</span> <span class="keyword">distinct</span> a.sid,b.score </span><br><span class="line"><span class="keyword">from</span> sc a ,sc b </span><br><span class="line"><span class="keyword">where</span> a.score=b.score </span><br><span class="line"><span class="keyword">and</span> a.cid&lt;&gt;b.cid;</span><br><span class="line"></span><br><span class="line">43、查询每门课程成绩最好的前两名：</span><br><span class="line"><span class="keyword">select</span> t1.sid <span class="keyword">as</span> 学生<span class="keyword">ID</span>,t1.cid  课程<span class="keyword">ID</span>, Score <span class="keyword">as</span> 分数</span><br><span class="line"><span class="keyword">from</span> sc t1 </span><br><span class="line"><span class="keyword">where</span> score <span class="keyword">in</span> (<span class="keyword">select</span> top <span class="number">2</span> score <span class="keyword">from</span> sc </span><br><span class="line">        <span class="keyword">where</span> t1.cid=cid</span><br><span class="line">        <span class="keyword">order</span> <span class="keyword">by</span> score <span class="keyword">desc</span>)</span><br><span class="line"><span class="keyword">order</span> <span class="keyword">by</span> t1.cid;</span><br><span class="line"></span><br><span class="line">44、统计每门课程的学生选修人数(超过10人的课程才统计)。要求输出课程号和选修人数，查询结果按人数降序排序，若人数相同，按课程号升序排序：</span><br><span class="line"><span class="keyword">select</span> cid <span class="keyword">as</span> 课程号,<span class="keyword">count</span>(*) <span class="keyword">as</span> 人数</span><br><span class="line"><span class="keyword">from</span> sc </span><br><span class="line"><span class="keyword">group</span> <span class="keyword">by</span> cid</span><br><span class="line"><span class="keyword">order</span> <span class="keyword">by</span> <span class="keyword">count</span>(*) <span class="keyword">desc</span> cid;</span><br><span class="line"></span><br><span class="line">45、检索至少选修两门课程的学生学号：</span><br><span class="line"><span class="keyword">select</span> <span class="keyword">sid</span> </span><br><span class="line"><span class="keyword">from</span> sc </span><br><span class="line"><span class="keyword">group</span> <span class="keyword">by</span> <span class="keyword">sid</span> </span><br><span class="line"><span class="keyword">having</span> <span class="keyword">count</span>(*)&gt;=<span class="number">2</span>;</span><br><span class="line"></span><br><span class="line">46、查询全部学生选修的课程和课程号和课程名：</span><br><span class="line"><span class="keyword">select</span> cid ,cname</span><br><span class="line"><span class="keyword">from</span> course </span><br><span class="line"><span class="keyword">where</span> cid <span class="keyword">in</span> (<span class="keyword">select</span> cid <span class="keyword">from</span> sc <span class="keyword">group</span> <span class="keyword">by</span> cid);</span><br><span class="line"></span><br><span class="line">47、查询没学过”叶平”老师讲授的任一门课程的学生姓名：</span><br><span class="line"><span class="keyword">select</span> sname </span><br><span class="line"><span class="keyword">from</span> student </span><br><span class="line"><span class="keyword">where</span> <span class="keyword">sid</span> <span class="keyword">not</span> <span class="keyword">in</span> (<span class="keyword">select</span> <span class="keyword">sid</span> <span class="keyword">from</span> course,teacher,sc <span class="keyword">where</span> course.tid=teacher.tid <span class="keyword">and</span> sc.cid=course.cid </span><br><span class="line"><span class="keyword">and</span> tname=<span class="string">'叶平'</span>);</span><br><span class="line"></span><br><span class="line">48、查询两门以上不及格课程的同学的学号以及其平均成绩：</span><br><span class="line"><span class="keyword">select</span> <span class="keyword">sid</span>,<span class="keyword">avg</span>(<span class="keyword">isnull</span>(score,<span class="number">0</span>)) </span><br><span class="line"><span class="keyword">from</span> sc </span><br><span class="line"><span class="keyword">where</span> <span class="keyword">sid</span> <span class="keyword">in</span> (<span class="keyword">select</span> <span class="keyword">sid</span> <span class="keyword">from</span> sc <span class="keyword">where</span> score&lt;<span class="number">60</span> <span class="keyword">group</span> <span class="keyword">by</span> <span class="keyword">sid</span> <span class="keyword">having</span> <span class="keyword">count</span>(*)&gt;<span class="number">2</span>)</span><br><span class="line"><span class="keyword">group</span> <span class="keyword">by</span> <span class="keyword">sid</span>;</span><br><span class="line"></span><br><span class="line">49、检索“004”课程分数小于60，按分数降序排列的同学学号：</span><br><span class="line"><span class="keyword">select</span> <span class="keyword">sid</span> </span><br><span class="line"><span class="keyword">from</span> sc </span><br><span class="line"><span class="keyword">where</span> cid=<span class="string">'004'</span> </span><br><span class="line"><span class="keyword">and</span> score&lt;<span class="number">60</span> </span><br><span class="line"><span class="keyword">order</span> <span class="keyword">by</span> score <span class="keyword">desc</span>;</span><br><span class="line"></span><br><span class="line">50、删除“002”同学的“001”课程的成绩：</span><br><span class="line">delect from sc </span><br><span class="line">where sid='002' </span><br><span class="line">and cid='001';</span><br></pre></td></tr></table></figure>
      
    </div>
    <footer class="article-footer">
      <a data-url="http://yoursite.com/2019/04/08/mysql/sql_基础使用/" data-id="ckcc5iwp00081q4uwkn8orzc8" class="article-share-link">Teilen</a>
      
      
  <ul class="article-tag-list"><li class="article-tag-list-item"><a class="article-tag-list-link" href="/tags/mysql/">mysql</a></li></ul>

    </footer>
  </div>
  
    
<nav id="article-nav">
  
    <a href="/2019/06/10/反转单链表/" id="article-nav-newer" class="article-nav-link-wrap">
      <strong class="article-nav-caption">Neuer</strong>
      <div class="article-nav-title">
        
          反转单链表
        
      </div>
    </a>
  
  
    <a href="/2019/04/06/java/java_GItChat_java/" id="article-nav-older" class="article-nav-link-wrap">
      <strong class="article-nav-caption">Älter</strong>
      <div class="article-nav-title">Java 基础Gchat</div>
    </a>
  
</nav>

  
</article>

</section>
        
          <aside id="sidebar">
  
    

  
    
  <div class="widget-wrap">
    <h3 class="widget-title">Tags</h3>
    <div class="widget">
      <ul class="tag-list"><li class="tag-list-item"><a class="tag-list-link" href="/tags/Design-Patterns/">Design Patterns</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/Git/">Git</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/Html/">Html</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/Http/">Http</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/JavaScript/">JavaScript</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/Sort/">Sort</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/algorithm/">algorithm</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/ali-weixin-pay/">ali/weixin pay</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/docker/">docker</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/elasticsearch/">elasticsearch</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/go/">go</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/hexo/">hexo</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/idea/">idea</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/java/">java</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/jvm/">jvm</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/linux/">linux</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/logstash/">logstash</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/mysql/">mysql</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/php/">php</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/pytest/">pytest</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/python/">python</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/springboot/">springboot</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/util/">util</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/vue/">vue</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/wechat/">wechat</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/我的世界/">我的世界</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/消息队列/">消息队列</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/线程/">线程</a></li></ul>
    </div>
  </div>


  
    
  <div class="widget-wrap">
    <h3 class="widget-title">Tag Cloud</h3>
    <div class="widget tagcloud">
      <a href="/tags/Design-Patterns/" style="font-size: 16.36px;">Design Patterns</a> <a href="/tags/Git/" style="font-size: 10px;">Git</a> <a href="/tags/Html/" style="font-size: 10px;">Html</a> <a href="/tags/Http/" style="font-size: 12.73px;">Http</a> <a href="/tags/JavaScript/" style="font-size: 15.45px;">JavaScript</a> <a href="/tags/Sort/" style="font-size: 18.18px;">Sort</a> <a href="/tags/algorithm/" style="font-size: 11.82px;">algorithm</a> <a href="/tags/ali-weixin-pay/" style="font-size: 13.64px;">ali/weixin pay</a> <a href="/tags/docker/" style="font-size: 17.27px;">docker</a> <a href="/tags/elasticsearch/" style="font-size: 10px;">elasticsearch</a> <a href="/tags/go/" style="font-size: 11.82px;">go</a> <a href="/tags/hexo/" style="font-size: 10px;">hexo</a> <a href="/tags/idea/" style="font-size: 10px;">idea</a> <a href="/tags/java/" style="font-size: 20px;">java</a> <a href="/tags/jvm/" style="font-size: 11.82px;">jvm</a> <a href="/tags/linux/" style="font-size: 18.18px;">linux</a> <a href="/tags/logstash/" style="font-size: 12.73px;">logstash</a> <a href="/tags/mysql/" style="font-size: 14.55px;">mysql</a> <a href="/tags/php/" style="font-size: 11.82px;">php</a> <a href="/tags/pytest/" style="font-size: 10px;">pytest</a> <a href="/tags/python/" style="font-size: 13.64px;">python</a> <a href="/tags/springboot/" style="font-size: 17.27px;">springboot</a> <a href="/tags/util/" style="font-size: 19.09px;">util</a> <a href="/tags/vue/" style="font-size: 10px;">vue</a> <a href="/tags/wechat/" style="font-size: 10.91px;">wechat</a> <a href="/tags/我的世界/" style="font-size: 10px;">我的世界</a> <a href="/tags/消息队列/" style="font-size: 10.91px;">消息队列</a> <a href="/tags/线程/" style="font-size: 11.82px;">线程</a>
    </div>
  </div>

  
    
  <div class="widget-wrap">
    <h3 class="widget-title">Archiv</h3>
    <div class="widget">
      <ul class="archive-list"><li class="archive-list-item"><a class="archive-list-link" href="/archives/2020/05/">May 2020</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2020/04/">April 2020</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2020/03/">March 2020</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2020/01/">January 2020</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2019/10/">October 2019</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2019/08/">August 2019</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2019/06/">June 2019</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2019/04/">April 2019</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2019/03/">March 2019</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2019/02/">February 2019</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2019/01/">January 2019</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2018/12/">December 2018</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2018/11/">November 2018</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2018/10/">October 2018</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2018/09/">September 2018</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2018/07/">July 2018</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2018/06/">June 2018</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2018/05/">May 2018</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2018/04/">April 2018</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2018/03/">March 2018</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2018/02/">February 2018</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2018/01/">January 2018</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2017/12/">December 2017</a></li></ul>
    </div>
  </div>


  
    
  <div class="widget-wrap">
    <h3 class="widget-title">letzter Beitrag</h3>
    <div class="widget">
      <ul>
        
          <li>
            <a href="/2020/05/17/linux/linux_shell/">linux—shell</a>
          </li>
        
          <li>
            <a href="/2020/05/16/linux/linux_awk_sed/">linux-awk\sed 示例</a>
          </li>
        
          <li>
            <a href="/2020/04/11/linux/linux_bash/">linux—bash</a>
          </li>
        
          <li>
            <a href="/2020/03/29/hello-world/">Hello World</a>
          </li>
        
          <li>
            <a href="/2020/03/16/go基础/">go基础知识</a>
          </li>
        
      </ul>
    </div>
  </div>

  
</aside>
        
      </div>
      <footer id="footer">
  
  <div class="outer">
    <div id="footer-info" class="inner">
      &copy; 2020 vchen<br>
      Powered by <a href="http://hexo.io/" target="_blank">Hexo</a>
    </div>
  </div>
</footer>
    </div>
    <nav id="mobile-nav">
  
    <a href="/" class="mobile-nav-link">Home</a>
  
    <a href="/archives" class="mobile-nav-link">Archives</a>
  
</nav>
    

<script src="//ajax.googleapis.com/ajax/libs/jquery/2.0.3/jquery.min.js"></script>


  <link rel="stylesheet" href="/fancybox/jquery.fancybox.css">
  <script src="/fancybox/jquery.fancybox.pack.js"></script>


<script src="/js/script.js"></script>



  </div>
</body>
</html>